package com.cjc.eas.dao.adminDao.adminStuDao;

import com.cjc.eas.domain.Student;
import com.cjc.eas.utils.ConnectDatabaseUtils;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


/**
 * @ClassName AdmStuInfor
 * @Description TODO 管理员对学生信息的管理
 * @Author fades
 * @Date 2021/5/29 10:55
 * @Version 1.0
 **/

public class AdmStuInfor {

    /*
    *静态代码块
    *获取数据库连接
     */
    static Connection conn = null;
    static{
        conn = ConnectDatabaseUtils.getConection();
    }
    /**
      *@Description 管理员查询学生信息--------输入任意个关键字，显示出所有匹配的行（条件必须与所有关键字都匹配）
      *@params
      *@return
      *@author fades
      *@since
      */
    public static List<Student> QueryStuInfor(Student student){

        List<Student> result = new ArrayList<>();
        int i=0;
        StringBuffer sql = new StringBuffer("select * from t_Student where 1 = 1");

        if(student.getStuSno() != null && student.getStuSno()  != ""){
            sql.append(" and stuSno like '%");
            sql.append(student.getStuSno());
            sql.append("%'");
        }
        if(student.getStuName() != null && student.getStuName() != ""){
            sql.append(" and stuName like '%");
            sql.append(student.getStuName());
            sql.append("%'");
        }
        if(student.getStuGender() != null && student.getStuGender()  != ""){
            sql.append(" and stuGender like '%");
            sql.append(student.getStuGender());
            sql.append("%'");
        }
        if(student.getStuID() != null && student.getStuID()  != ""){
            sql.append(" and stuID like '%");
            sql.append(student.getStuID());
            sql.append("%'");
        }
        if(student.getStuClassNum() != null && student.getStuClassNum()  != ""){
            sql.append(" and stuClassNum like '%");
            sql.append(student.getStuClassNum());
            sql.append("%'");
        }
        if(student.getStuDepartment() != null && student.getStuDepartment() != ""){
            sql.append(" and stuDepartment like '%");
            sql.append(student.getStuDepartment());
            sql.append("%'");
        }
        if(student.getStuEnrollmentYear() != null && student.getStuEnrollmentYear()  != ""){
            sql.append(" and stuEnrollmentYear like '%");
            sql.append(student.getStuEnrollmentYear());
            sql.append("%'");
        }
        if(student.getStuEduLevel() != null && student.getStuEduLevel() != ""){
            sql.append(" and stuEduLevel like '%");
            sql.append(student.getStuEduLevel());
            sql.append("%'");
        }
        if(student.getStuMajor() != null && student.getStuMajor()  != ""){
            sql.append(" and stuMajor like '%");
            sql.append(student.getStuMajor());
            sql.append("%'");
        }
        if(student.getStuNativePlace() != null && student.getStuNativePlace() != ""){
            sql.append(" and stuNativePlace like '%");
            sql.append(student.getStuNativePlace());
            sql.append("%'");
        }
        try{
//            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while(rs.next()){
                String stuSno=rs.getString("stuSno");      //学号
                String stuName=rs.getString("stuName");         //姓名
                String stuGender=rs.getString("stuGender");   //性别
                String stuDateOfBirth=rs.getString("stuDateOfBirth");  //出生日期
                String stuID=rs.getString("stuID");       //身份证号
                String stuClassNum=rs.getString("stuClassNum"); //班级
                String stuNation=rs.getString("stuNation");   //民族
                String stuDepartment=rs.getString("stuDepartment");   //院系
                String stuContactNum=rs.getString("stuContactNum");   //联系电话
                String stuContactAddr=rs.getString("stuContactAddr");  //联系地址
                String stuContactPerson=rs.getString("stuContactPerson");    //联系人
                String stuEnrollmentYear=rs.getString("stuEnrollmentYear");   //入学年份
                String stuEduLevel=rs.getString("stuEduLevel");     //培养层次
                String stuMajor=rs.getString("stuMajor");    //专业
                String stuLengthOfStudy=rs.getString("stuLengthOfStudy");    //学制
                String stuPostcode=rs.getString("stuPostcode");     //邮政编码
                String stuNativePlace=rs.getString("stuNativePlace");  //籍贯
                String stuNativePlaceType=rs.getString("stuNativePlaceType");  //户籍类型
                String stuEmail=rs.getString("stuEmail");    //邮箱地址
//                String stuAccount=rs.getString("stuAccount");  //账户
//                String stuPasswd=rs.getString("stuPasswd");
                Student student1=new Student();
                student1.setStuSno(stuSno);
                student1.setStuName(stuName);
                student1.setStuGender(stuGender);
                student1.setStuDateOfBirth(stuDateOfBirth);
                student1.setStuID(stuID);
                student1.setStuClassNum(stuClassNum);
                student1.setStuNation(stuNation);
                student1.setStuDepartment(stuDepartment);
                student1.setStuContactNum(stuContactNum);
                student1.setStuContactAddr(stuContactAddr);
                student1.setStuContactPerson(stuContactPerson);
                student1.setStuEnrollmentYear(stuEnrollmentYear);
                student1.setStuEduLevel(stuEduLevel);
                student1.setStuMajor(stuMajor);
                student1.setStuLengthOfStudy(stuLengthOfStudy);
                student1.setStuPostcode(stuPostcode);
                student1.setStuNativePlace(stuNativePlace);
                student1.setStuNativePlaceType(stuNativePlaceType);
                student1.setStuEmail(stuEmail);
                result.add(student1);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return result;
    }
    /**
      *@Description 单元测试
      *@params
      *@return
      *@author fades
      *@since
      */
    @Test
    public  void Tes() {
        Student testStu = new Student();
        testStu.setStuNativePlace("河南");
        testStu.setStuSno("19");
        List<Student> testResult = QueryStuInfor(testStu);
        for(Student e : testResult){
            System.out.println(e.getStuName());
        }

    }
}
